﻿Imports MySql.Data.MySqlClient
Imports System.Data.SqlServerCe
Imports System.Data.SqlServerCe.SqlCeEngine
Imports System.Deployment.Application

Public Class conlocal
    Implements IDisposable

    Private Shared strCommon As String

    Public Shared Function NewConnection() As conlocal

        Try
            strCommon = "Data Source=\\ryh1\IT\FRIM.sdf"
            'strCommon = "Data Source=D:\FRIM.sdf"

        Catch ex As Exception
            strCommon = "Data Source=\\ryh1\IT\FRIM.sdf"
            'strCommon = "Data Source=D:\FRIM.sdf"
            'strCommon = "Data Source=" & Application.StartupPath & "\FRIM.sdf"
            MsgBox(ex.ToString)
        End Try
        Return New conlocal(strCommon)
    End Function

    Public mConnection As SqlCeConnection
    Private mTransaction As SqlCeTransaction
    Private mIsInTransaction As Boolean

    Public Sub New(ByVal connectionString As String)
        mConnection = New SqlCeConnection(connectionString)
        mIsInTransaction = False
        mConnection.Open()
    End Sub

    Public Sub BeginTrans()
        If (Not mIsInTransaction) Then
            mTransaction = mConnection.BeginTransaction()
            mIsInTransaction = True
        End If
    End Sub

    Public Sub RollbackTrans()
        If (mIsInTransaction) Then
            mTransaction.Rollback()
            mTransaction.Dispose()
            mIsInTransaction = False
        End If
    End Sub

    Public Sub CommitTrans()
        If (mIsInTransaction) Then
            mTransaction.Commit()
            mTransaction.Dispose()
            mIsInTransaction = False
        End If
    End Sub

    Public Function ExecuteNonQuery(ByVal sql As String) As Integer
        Try
            Dim cmd As New SqlCeCommand
            cmd = Me.mConnection.CreateCommand
            cmd.CommandText = sql
            Dim result As Integer
            If (Me.mIsInTransaction) Then
                Using cmd
                    cmd.Transaction = Me.mTransaction
                    cmd.Connection = Me.mConnection
                    cmd.CommandType = CommandType.Text
                    result = cmd.ExecuteNonQuery()
                End Using
            Else
                Using cmd
                    cmd.Connection = Me.mConnection
                    cmd.CommandType = CommandType.Text
                    result = cmd.ExecuteNonQuery()
                End Using
            End If
            Return result
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Function

    Public Function ExecuteScalar(ByVal sql As String) As Object

        Dim result As Object
        If (Me.mIsInTransaction) Then
            Using cmd As New SqlCeCommand(sql, Me.mConnection, Me.mTransaction)
                result = cmd.ExecuteScalar()
            End Using
        Else
            Using cmd As New SqlCeCommand(sql, Me.mConnection)
                result = cmd.ExecuteScalar()
            End Using
        End If
        Return result
    End Function

    Public Function GetTable(ByVal sql As String) As DataTable
   
        Dim result As New DataTable()
        If (Me.mIsInTransaction) Then
            Using selectCommand As New SqlCeCommand(sql, Me.mConnection, Me.mTransaction)
                Using adapter As New SqlCeDataAdapter(selectCommand)
                    adapter.Fill(result)
                End Using
            End Using
        Else
            Using adapter As New SqlCeDataAdapter(sql, Me.mConnection)
                adapter.Fill(result)
            End Using
        End If
        Return result
    End Function





#Region "IDisposable Support"
    Private disposedValue As Boolean

    Protected Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
            End If

            If (mIsInTransaction) Then
                mTransaction.Rollback()
                mTransaction.Dispose()
            End If
            mConnection.Close()
            mConnection.Dispose()

        End If
        Me.disposedValue = True
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region



End Class
